/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Tour;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBUtil;

/**
 *
 * @author lab
 */
public class TourDAO {

    String SQL_CREATE_TOUR = "INSERT INTO [Tour]( TourCateID, Name, ImageUrl, Price, Description) VALUES(?,?,?,?,?)";
    String SQL_READ_ALL_TOUR = "SELECT * FROM [Tour]";
    String SQL_UPDATE_TOUR = "UPDATE [Tour] SET TourCateID=?, Name =?, ImageUrl =?, Price =?, Description = ? WHERE TourID = ?";
    String SQl_DELETE_TOUR = "DELETE from [Tour] WHERE TourID = ?";
    String SQL_READ_TOUR_BY_ID = "SELECT * FROM [Tour]";
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    public String mesage = "";

    public TourDAO() {
    }

    public boolean  createTour(Tour tour) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_CREATE_TOUR);
            pst.setInt(1, tour.getTourCate().getTourCateID());
            pst.setString(2, tour.getName());
            pst.setString(3, tour.getImageUrl());
            pst.setDouble(4, tour.getPrice());
            pst.setString(5, tour.getDescription());
            if (pst.executeUpdate() > 0) {
                mesage = "Create Tour successfully!";
                result = true;
            } else {
                mesage = "Create Tour unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public boolean  updateTour(Tour tour) {
         boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_UPDATE_TOUR);
            pst.setInt(1, tour.getTourCate().getTourCateID());
            pst.setString(2, tour.getName());
            pst.setString(3, tour.getImageUrl());
            pst.setDouble(4, tour.getPrice());
            pst.setInt(5, tour.getTourID());

            if (pst.executeUpdate() > 0) {
                result = true;
                mesage = "Update Tour Category successfully!";
            } else {
                mesage = "Update Tour Category unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public boolean  deleteTour(int tourid) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQl_DELETE_TOUR);
            pst.setInt(1, tourid);
            if (pst.executeUpdate() > 0) {
                result = true;
                mesage = "Delete successfully!";
            } else {
                mesage = "Delete unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public ArrayList<Tour> getAllTour() {
        ArrayList<Tour> tours = new ArrayList<Tour>();
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_ALL_TOUR);
            rs = pst.executeQuery();
            while (rs.next()) {
                Tour tour = new Tour();
                tour.setTourID(rs.getInt("TourID"));
                int tourCateID = rs.getInt("TourCateID");
                TourCateDAO tourCateDAO = new TourCateDAO();
                tour.setTourCate(tourCateDAO.getTourCateByID(tourCateID));
                tour.setName(rs.getString("Name"));
                tour.setImageUrl(rs.getString("ImageUrl"));
                tour.setPrice(rs.getDouble("Price"));
                tour.setDescription(rs.getString("Description"));
                tours.add(tour);
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return tours;
    }

    public Tour getTour(int tourID) {
        Tour tour = null;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_TOUR_BY_ID);
            pst.setInt(1, tourID);
            rs = pst.executeQuery();
            if (rs.next()) {
                tour = new Tour();
                tour.setTourID(rs.getInt("TourID"));
                int tourCateID = rs.getInt("TourCateID");
                TourCateDAO tourCateDAO = new TourCateDAO();
                tour.setTourCate(tourCateDAO.getTourCateByID(tourCateID));
                tour.setName(rs.getString("Name"));
                tour.setImageUrl(rs.getString("ImageUrl"));
                tour.setPrice(rs.getDouble("Price"));
                tour.setDescription(rs.getString("Description"));                
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return tour;
    }
}
